﻿--内部邮件
create table Mail
(
	ID int		primary key ,
	[Name]		varchar(100),
	Content		varchar(5000),
	CreatorID	int not null,
	CreatorName varchar(100) not null,
	RecieveIDs	varchar(200),
	RecieveNames varchar(200),
	CopyToIDs	varchar(200),
	CopyToNames varchar(200),
	AskReceipt bit not null default 0,
	InputTime	datetime not null default getdate()
)
go
create table MailAttach
(
	[ID] [int] primary key NOT NULL,
	[Name] [varchar](200) NOT NULL,
	[BindID] [int] foreign key references [dbo].[Mail]([ID]) NOT NULL,
	[Path] [varchar](100) not null,
	[Type] [varchar](100) not null,
	[Size] [int] NOT NULL,
	[InputTime] [datetime] not null DEFAULT (getdate()),
	[Flag] [int] NULL,
	[CreatorID] [int] NULL,
	[CreatorName] [nvarchar](20) NULL,
)


go
create table MailUser
(
	ID      uniqueidentifier ROWGUIDCOL   primary key default newid(),
	MailID	int foreign key references Mail(ID) on delete cascade not null,
	UserID	int not null,
	FolderID	int not null,
	Status  int not null default 0
)
go
create table MailFolder
(
	ID int primary key ,
	[Name] varchar(50) not null,
	UserID int not null foreign key references [User](ID) on delete cascade,
	TypeID int,
)
go
create table WebMailAccount
(
	ID int primary key,
	UserID int foreign key references [User](ID) on delete cascade,
	DispName nvarchar(10) not null,
	MailAddr varchar(100) not null unique,
	Pop3Server varchar(50) not null,
	Pop3Port int not null,
	Pop3User varchar(50) not null,
	Pop3Pwd varchar(50) not null,
	SmtpServer varchar(50) not null,
	SmtpPort int not null,
	SmtpUser varchar(50),
	SmtpPwd varchar(50),
	SmtpAuthenicate bit not null default 1,
	ServerType int not null,
	IsDefault bit not null default 0
)
go
create table WebMail
(
	ID int primary key,
	[Name] nvarchar(1000),
	Body ntext,
	BodySize int,
	UserID int,
	MailGuid varchar(100),
	FolderID int,
	FromName nvarchar(100),
	FromAddr varchar(100),
	ToAddr varchar(2000),
	CCAddr varchar(2000),
	AccountAddr varchar(100) not null foreign key references WebMailAccount(MailAddr) on delete cascade on update cascade,
	InputTime datetime,
	HasAttachment bit not null default 0,
	HasDownload bit not null default 0,
	Status int,
)
go
create table WebMailAttach
(
	[ID] [int] primary key NOT NULL,
	[Name] [varchar](200) NOT NULL,
	[BindID] [int] foreign key references [dbo].[WebMail]([ID]) NOT NULL,
	[Path] [varchar](100) not null,
	[Type] [varchar](100) not null,
	[Size] [int] NOT NULL,
	[InputTime] [datetime] not null DEFAULT (getdate()),
	[Flag] [int] NULL,
	[CreatorID] [int] NULL,
	[CreatorName] [nvarchar](20) NULL,
)

go